package table_data;


import beans.Formal;
import common.Utils;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import static beans.Formal.setTable_names;
import static beans.Formal.table_names;

public class TransFormalKbaseToLocal {
    public static void main(String[] args) {
        setTable_names();
        for(int i=0;i<=table_names.size()-1;i++) {
            try {
                File file = new File("C:\\Users\\ts647\\Desktop\\formal\\"+table_names.get(i));//文件不存在则创建
                FileOutputStream fos = new FileOutputStream(file, true);//第二个参数为true代表追加，为false(默认)为覆盖
                Class.forName(Utils.driver);//加载KBase驱动
                Connection conn = DriverManager.getConnection(Utils.formal_url, Utils.user, Utils.password_220);// 获取连接
                Statement statement = conn.createStatement();//得到声明
                //当kbase某列值为null（看起来就是空）时，该列也存在，例子："unit_type_code":""
                ResultSet rs = statement.executeQuery(String.format("SELECT * FROM %s",table_names.get(i)));//执行SQL语句返回结果集
                while (rs.next()) { //循环输出结果集
                    String id = rs.getString("ID");
                    String years = rs.getString("年份");
                    String areas = rs.getString("地区");
                    String zheng_shi_indicator = rs.getString("正式指标");
                    String zheng_shi_value = rs.getString("正式数值");
                    String zheng_shi_unit = rs.getString("正式单位");
                    String yuan_shi_value = rs.getString("原始数值");
                    String yuan_shi_unit = rs.getString("原始单位");
                    String zhi_shi_yuan = rs.getString("知识元");

                    String nei_rong_zhu_shi = rs.getString("内容注释");
                    String wen_ben_wen_jian_ming = rs.getString("文本文件名");
                    String nian_jian_zhong_wen_ming = rs.getString("年鉴中文名");
                    String nian_jian_nian_fen = rs.getString("年鉴年份");
                    String di_qu_dai_ma = rs.getString("地区代码");
                    String di_qu_deng_ji = rs.getString("地区等级");
                    String mo_hu_di_qu = rs.getString("模糊地区");
                    String jian_suo_zhi_biao = rs.getString("检索指标");
                    String mo_hu_zhi_biao = rs.getString("模糊指标");
                    String exclusive_file_name = rs.getString("EXCLUSIVEFILENAME");
                    String ru_ku_biao_shi = rs.getString("入库标识");
                    String excel_title = rs.getString("EXCEL标题");
                    String excel_dai_ma = rs.getString("EXCEL年鉴代码");
                    String validtag = rs.getString("VALIDTAG");
                    String nian_jian_name = rs.getString("年鉴名称");
                    String nian_jian_ying_wen_ming_cheng = rs.getString("年鉴英文名称");
                    String zhi_biao_lei_bie = rs.getString("指标类别");
                    String zhi_biao_pai_xu = rs.getString("指标排序");
                    String jing_lian_biao_ti = rs.getString("精炼标题");
                    String di_yu_lei_xing_dai_ma = rs.getString("地域类型代码");
                    String pin_yin_kan_ming = rs.getString("拼音刊名");
                    String wen_jian_ming = rs.getString("文件名");
                    String bian_hao = rs.getString("编号");
                    String tiao_mu_ti_ming = rs.getString("条目题名");
                    String yin_shua_ye_ma = rs.getString("印刷页码");
                    String fu_qin_jie_dian = rs.getString("父亲节点");
                    String tm_mo_hu_zhi_biao = rs.getString("TM_模糊指标");
                    String zhuan_ji_dai_ma = rs.getString("专辑代码");
                    String reportgrouper = rs.getString("REPORTGROUPER").replace("?", "€");
                    String xian_shi_shu_zhi = rs.getString("显示数值");
                    String xian_shi_dan_wei = rs.getString("显示单位");
                    String calibertag = rs.getString("CALIBERTAG");
                    String wen_jian_mingx = rs.getString("文件名X");
                    String nian_jian_zhong_wen_mingx = rs.getString("年鉴中文名X");
                    String nian_jian_nian_fenx = rs.getString("年鉴年份X");
                    String tiao_mu_ti_mingx = rs.getString("条目题名X");
                    String bian_haox = rs.getString("编号X");
                    String ru_ku_biao_shix = rs.getString("入库标识X");
                    String yin_shua_yex = rs.getString("印刷页码X");
                    String fu_qin_jie_dianx = rs.getString("父亲节点X");
                    String guang_pan_haox = rs.getString("光盘号");
                    String guo_biao_di_yu_dai_ma = rs.getString("国标地域代码");
                    Formal ybp = new Formal(id, years, areas, zheng_shi_indicator, zheng_shi_value, zheng_shi_unit, yuan_shi_value, yuan_shi_unit, zhi_shi_yuan, nei_rong_zhu_shi, wen_ben_wen_jian_ming, nian_jian_zhong_wen_ming, nian_jian_nian_fen, di_qu_dai_ma, di_qu_deng_ji, mo_hu_di_qu, jian_suo_zhi_biao, mo_hu_zhi_biao, exclusive_file_name, ru_ku_biao_shi, excel_title, excel_dai_ma, validtag, nian_jian_name, nian_jian_ying_wen_ming_cheng, zhi_biao_lei_bie, zhi_biao_pai_xu, jing_lian_biao_ti, di_yu_lei_xing_dai_ma, pin_yin_kan_ming, wen_jian_ming, bian_hao, tiao_mu_ti_ming, yin_shua_ye_ma, fu_qin_jie_dian, tm_mo_hu_zhi_biao, zhuan_ji_dai_ma, reportgrouper, xian_shi_shu_zhi, xian_shi_dan_wei, calibertag, wen_jian_mingx, nian_jian_zhong_wen_mingx, nian_jian_nian_fenx, tiao_mu_ti_mingx, bian_haox, ru_ku_biao_shix, yin_shua_yex, fu_qin_jie_dianx, guang_pan_haox, guo_biao_di_yu_dai_ma);
                    fos.write(ybp.toString().getBytes());
                }
                System.out.println(table_names.get(i)+" is finished");
                fos.close();
                rs.close();//关闭结果集
                conn.close();//关闭数据库连接
            } catch (Exception e) {
                System.out.print("KBase ERROR:" + e.getMessage());
            }
        }
    }
}
